const mysql = require('mysql2/promise');

// 数据库配置
const dbConfig = {
  host: process.env.DB_HOST || "43.143.253.188",
  port: parseInt(process.env.DB_PORT || "3306", 10),
  user: process.env.DB_USER || "nextjs",
  password: process.env.DB_PASS || "yEAiRE2jcxTyRBy5",
  database: process.env.DB_NAME || "nextjs",
  charset: 'utf8mb4',
  timezone: '+08:00',
};

// 地图数据 - 根据文档中的标准数据构建
const mapData = [
  // 顶级区域 (用于1418查询)
  { id: 100, name: "中原地区", parent_id: null, extension: null },
  { id: 19, name: "蜀地", parent_id: null, extension: null },
  { id: 43, name: "江东地区", parent_id: null, extension: null },
  
  // 中原地区的城市 (areaId=100的子区域)
  { id: 289, name: "许昌", parent_id: 100, extension: null },
  { id: 302, name: "洛阳", parent_id: 100, extension: null },
  { id: 304, name: "长安", parent_id: 100, extension: null },
  
  // 蜀地的城市 (areaId=19的子区域)
  { id: 70, name: "成都", parent_id: 19, extension: null },
  { id: 111, name: "南中", parent_id: 19, extension: null },
  { id: 117, name: "汉中", parent_id: 19, extension: null },
  
  // 江东地区的城市 (areaId=43的子区域)
  { id: 154, name: "建业", parent_id: 43, extension: null },
  { id: 212, name: "长沙", parent_id: 43, extension: null },
  { id: 264, name: "江陵", parent_id: 43, extension: null },
  
  // 许昌的子区域 (pid=289)
  { id: 2, name: "新手村", parent_id: 289, extension: "(1 级)" },
  { id: 301, name: "陈留", parent_id: 289, extension: "(5 级)" },
  { id: 487, name: "九里山", parent_id: 289, extension: "(20 级)" },
  { id: 533, name: "芒砀山", parent_id: 289, extension: "(30 级)" },
  
  // 洛阳的子区域 (pid=302)
  { id: 285, name: "官渡", parent_id: 302, extension: "(40 级)" },
  { id: 286, name: "虎牢关", parent_id: 302, extension: "(50 级)" },
  { id: 333, name: "乌巢", parent_id: 302, extension: "(80 级)" },
  { id: 337, name: "函谷关", parent_id: 302, extension: "(55 级)" },
  
  // 长安的子区域 (pid=304)
  { id: 303, name: "华山", parent_id: 304, extension: "(90 级)" },
  { id: 311, name: "潼关", parent_id: 304, extension: "(75 级)" },
  { id: 414, name: "五丈原", parent_id: 304, extension: "(65 级)" },
  
  // 成都的子区域 (pid=70)
  { id: 93, name: "落凤坡", parent_id: 70, extension: "(30 级)" },
  { id: 112, name: "广汉", parent_id: 70, extension: "(40 级)" },
  { id: 113, name: "峨眉山", parent_id: 70, extension: "(50 级)" },
  
  // 南中的子区域 (pid=111)
  { id: 52, name: "云南", parent_id: 111, extension: "(60 级)" },
  { id: 143, name: "泸水", parent_id: 111, extension: "(70 级)" },
  { id: 351, name: "西洱湖", parent_id: 111, extension: "(80 级)" },
  
  // 汉中的子区域 (pid=117)
  { id: 306, name: "子午谷", parent_id: 117, extension: "(90 级)" },
  { id: 307, name: "街亭", parent_id: 117, extension: "(100 级)" },
  { id: 308, name: "上方谷", parent_id: 117, extension: "(120 级)" },
  
  // 建业的子区域 (pid=154)
  { id: 267, name: "广陵", parent_id: 154, extension: null },
  
  // 长沙的子区域 (pid=212)
  { id: 220, name: "巴陵", parent_id: 212, extension: null },
  { id: 221, name: "洞庭湖", parent_id: 212, extension: null },
  { id: 222, name: "湘水", parent_id: 212, extension: "(130 级)" },
  
  // 江陵的子区域 (pid=264)
  { id: 137, name: "麦城", parent_id: 264, extension: "(140 级)" },
  { id: 161, name: "长坂坡", parent_id: 264, extension: "(140 级)" },
  { id: 163, name: "夷陵", parent_id: 264, extension: null },
];

async function importMapData() {
  let connection;
  
  try {
    console.log('🚀 开始导入地图数据...');
    
    // 连接数据库
    connection = await mysql.createConnection(dbConfig);
    console.log('✅ 数据库连接成功！');
    
    // 检查表是否存在
    const [tables] = await connection.execute("SHOW TABLES LIKE 'map_areas'");
    if (tables.length === 0) {
      console.log('❌ map_areas 表不存在，请先创建表结构');
      return;
    }
    
    console.log('✅ map_areas 表存在');
    
    // 清空现有数据
    console.log('🗑️ 清空现有地图数据...');
    await connection.execute('DELETE FROM map_areas');
    console.log('✅ 现有数据已清空');
    
    // 插入新数据
    console.log('📥 开始导入地图数据...');
    
    const insertSql = `
      INSERT INTO map_areas (id, name, parent_id, extension, created_at, updated_at)
      VALUES (?, ?, ?, ?, NOW(), NULL)
    `;
    
    let successCount = 0;
    let errorCount = 0;
    
    for (const area of mapData) {
      try {
        const values = [
          area.id,
          area.name,
          area.parent_id,
          area.extension,
        ];
        
        await connection.execute(insertSql, values);
        console.log(`✅ 导入地图: ${area.name} (ID: ${area.id}, 父ID: ${area.parent_id || 'NULL'})`);
        successCount++;
        
      } catch (error) {
        console.error(`❌ 导入地图失败: ${area.name} (ID: ${area.id})`, error.message);
        errorCount++;
      }
    }
    
    console.log('\n📊 导入结果统计:');
    console.log(`✅ 成功导入: ${successCount} 条记录`);
    console.log(`❌ 导入失败: ${errorCount} 条记录`);
    console.log(`📈 总计处理: ${mapData.length} 条记录`);
    
    // 验证导入结果
    console.log('\n🔍 验证导入结果...');
    const [results] = await connection.execute('SELECT COUNT(*) as count FROM map_areas');
    console.log(`📊 数据库中现有地图数量: ${results[0].count}`);
    
    // 按层级显示数据
    console.log('\n📋 地图层级结构:');
    
    // 顶级区域
    const [topLevel] = await connection.execute(`
      SELECT id, name FROM map_areas WHERE parent_id IS NULL ORDER BY id ASC
    `);
    
    for (const top of topLevel) {
      console.log(`\n🏛️ ${top.name} (ID: ${top.id})`);
      
      // 二级区域（城市）
      const [cities] = await connection.execute(`
        SELECT id, name FROM map_areas WHERE parent_id = ? ORDER BY id ASC
      `, [top.id]);
      
      for (const city of cities) {
        console.log(`  🏙️ ${city.name} (ID: ${city.id})`);
        
        // 三级区域（具体地点）
        const [locations] = await connection.execute(`
          SELECT id, name, extension FROM map_areas WHERE parent_id = ? ORDER BY id ASC
        `, [city.id]);
        
        for (const location of locations) {
          const displayName = location.extension ? `${location.name}${location.extension}` : location.name;
          console.log(`    📍 ${displayName} (ID: ${location.id})`);
        }
      }
    }
    
    console.log('\n🎉 地图数据导入完成！');
    
  } catch (error) {
    console.error('❌ 导入过程中发生错误:', error);
    throw error;
  } finally {
    if (connection) {
      await connection.end();
      console.log('🔌 数据库连接已关闭');
    }
  }
}

// 执行导入
if (require.main === module) {
  importMapData()
    .then(() => {
      console.log('✅ 脚本执行完成');
      process.exit(0);
    })
    .catch((error) => {
      console.error('❌ 脚本执行失败:', error);
      process.exit(1);
    });
}

module.exports = { importMapData, mapData };
